package com.fh.db;

import com.fh.common.exception.SelfDefinedException;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author qiujunda
 * @Date 16/05/2020
 * @Description
 */
public class SqlRunner {

    public static void testMysql() throws Exception {
        DbSource dbSource = (DbSource) Class.forName("com.fh.db.mysql.MysqlDbSource").newInstance();
        dbSource.initSource("mysql", "jdbc:mysql://localhost:3307/test?serverTimezone=GMT%2B8&allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false",
                "root", "1987920", "com.mysql.jdbc.Driver");
        DbSchema dbSchema = dbSource.getSchema();
        List<DbTable> list = dbSchema.getTables();
        for (DbTable dbTable : list) {
            List<DbColumn> columns = dbTable.getColumns();
            System.out.println("1");
        }
        System.out.println(list.size());
    }

    public static void main(String[] args) throws Exception {
        testMysql();
        String sql = "select * from cux_qppt_headers_all";
        DbSource dbSource = (DbSource) Class.forName("com.fh.db.oracle.OracleDbSource").newInstance();
        dbSource.initSource("oracle", "jdbc:oracle:thin:@10.2.183.131:1531:UAT", "APPS", "APPS", "oracle.jdbc.OracleDriver");
        DbSchema dbSchema = dbSource.getSchema();
        //query package
//        List<DbPackage> list=dbSchema.getPackages();
//        list.forEach(dbPackage -> {
////            System.out.println(dbPackage.getOwner()+"--"+dbPackage.getPackageName());
//            if(dbPackage.getPackageName().equals("CUX_EXCEL_JAVA_PKG")){
////                List<DbPackageDeclare> dbPackageDeclareList=dbPackage.getDbPackageDeclare();
////                dbPackageDeclareList.forEach(dbPackageDeclare -> {
////                    System.out.println(dbPackageDeclare.getLineId()+"---"+dbPackageDeclare.getContent());
////                });
//                List<DbPackageBody> dbPackageBodyList=dbPackage.getDbPackageBody();
//                dbPackageBodyList.forEach(dbPackageBody -> {
//                    System.out.println(dbPackageBody.getLineId()+"---"+dbPackageBody.getContent());
//                });
//            }
//        });
        //query package end

        //query table
        List<DbTable> list = dbSchema.getTables();
        list.forEach(dbTable -> {
            if (dbTable.getTableName().equals("CUX_EXCEL_OPT_LIST")) {
                List<DbIndex> index = dbTable.getIndexs();
                index.forEach(dbIndex -> {
                    List<DbIndexColumn> indexColumn = dbIndex.getDbIndexColumn();
                    System.out.println(indexColumn.size());
                });
                System.out.println(index);
                //query column
                List<DbColumn> columns = dbTable.getColumns();
                columns.forEach(dbColumn -> {
                    System.out.println(dbColumn.getDataType() + "-" + dbColumn.getDataPrecision());
                });
                System.out.println(columns.size());
            }
            System.out.println(dbTable.getTableName() + "--" + dbTable.getTablespaceName() + "--" + dbTable.getRowNum());
        });

        //query table end

        //query view
//        List<DbView> list = dbSchema.getViews(dbSource);
//        list.forEach(dbView -> {
//            if (dbView.getViewName().equals("CUX_SUBSCRIPTION_IQM_V")) {
//                System.out.println(dbView.getTextLen() + "-----------" + dbView.getViewName());
//                System.out.println(dbView.getTextVc());
//            }
//
//        });
        //query view end

//        querySqlColumns(dbSource, sql);
    }

    public static void runUpdateSql(DbSource source, String sql) {
        Connection connection = null;
        try {
            connection = source.getConnection();
            Statement statement = connection.createStatement();
            statement.executeUpdate(sql);
        } catch (ClassNotFoundException e) {
            throw new SelfDefinedException("数据库驱动不支持：" + e.getMessage());
        } catch (SQLException e) {
            throw new SelfDefinedException("SQL不对：" + e.getMessage());
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                throw new SelfDefinedException("数据库无法连接：" + e.getMessage());
            }
        }
    }

    /**
     * 执行sql，并通过ResultHandler对ResultSet结果集进行封装
     *
     * @param source
     * @param sql
     * @param handler
     * @return
     */
    public static Object sqlResult(DbSource source, String sql, ResultHandler handler) {
        Connection connection = null;
        try {
            connection = source.getConnection();
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(sql);
            Object result = handler.handle(resultSet);
            return result;
        } catch (ClassNotFoundException e) {
            throw new SelfDefinedException("数据库驱动不支持：" + e.getMessage());
        } catch (SQLException e) {
            throw new SelfDefinedException("SQL不对：" + e.getMessage());
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                throw new SelfDefinedException("数据库无法连接：" + e.getMessage());
            }
        }
    }

    /**
     * 执行预编译sql，并通过ResultHandler对ResultSet结果集进行封装
     *
     * @param source
     * @param sql
     * @param params
     * @param handler
     * @return
     */
    public static Object preparedSqlResult(DbSource source, String sql, Object[] params, ResultHandler handler) {
        Connection connection = null;
        try {
            connection = source.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            for (int i = 1; i <= params.length; i++) {
                preparedStatement.setObject(i, params[i - 1]);
            }
            ResultSet resultSet = preparedStatement.executeQuery();
            return handler.handle(resultSet);
        } catch (ClassNotFoundException e) {
            throw new SelfDefinedException("数据库驱动不支持：" + e.getMessage());
        } catch (SQLException e) {
            throw new SelfDefinedException("SQL不对：" + e.getMessage());
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                throw new SelfDefinedException("数据库无法连接：" + e.getMessage());
            }
        }
    }

    public static List<SqlColumn> querySqlColumns(DbSource dbSource, String sql, List<String> includeFields, List<String> fieldTexts) throws SQLException, ClassNotFoundException {
        String runSql = "select runsql.* from (" + sql + ")runsql where 1<>1";
        return (List<SqlColumn>) sqlResult(dbSource, runSql, resultSet -> {
            List<SqlColumn> result = new ArrayList<>();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            boolean filter = (includeFields == null || fieldTexts == null) ? false : true;
            if (filter) {
                int includeLen = includeFields.size();
                int textLen = fieldTexts.size();
                if (includeLen == 0 || includeLen == 0 || includeLen != includeLen) {
                    filter = false;
                }
            }

            for (int i = 1; i <= columnCount; i++) {
                String fieldName = metaData.getColumnName(i);
                if (filter && includeFields.contains(fieldName)) {
                    result.add(new SqlColumn(i, fieldName, metaData.getColumnClassName(i), fieldTexts.get(i - 1)));
                } else {
                    result.add(new SqlColumn(i, fieldName, metaData.getColumnClassName(i), ""));
                }

            }
            return result;
        });
    }

    /**
     * @param dbSource
     * @param sql
     * @return java.util.List<com.fh.db.SqlColumn>
     * @author qiujunda
     * @description 查询sql返回结果字段属性
     * @date 09/10/2020 17:32
     */
    public static List<SqlColumn> querySqlColumns(DbSource dbSource, String sql) throws SQLException, ClassNotFoundException {
        return querySqlColumns(dbSource, sql, null, null);
    }

    /**
     * @param dbSource
     * @param sql
     * @param offset
     * @param limit
     * @return java.util.List<java.util.Map>
     * @author qiujunda
     * @description 执行sql，并返回结果，必须有分页数据
     * @date 09/10/2020 18:55
     */
    public static List<Map> querySqlData(DbSource dbSource, String sql, int offset, int limit) {
        String querySql = dbSource.getPageSql(sql, offset, limit);
        return (List<Map>) sqlResult(dbSource, querySql, resultSet -> {
            List<Map> result = new ArrayList<>();
            ResultSetMetaData md = resultSet.getMetaData();
            int columnCount = md.getColumnCount();
            while (resultSet.next()) {
                Map<String, Object> rowData = new HashMap<String, Object>();
                for (int i = 1; i <= columnCount; i++) {
                    rowData.put(md.getColumnName(i), resultSet.getObject(i));
                }
                result.add(rowData);
            }
            return result;
        });
    }

    public static String convertOracleLongColumn(InputStream inputStream) throws Exception {
        ByteArrayOutputStream infoStream = new ByteArrayOutputStream();
        int len = 0;
        byte[] bytes = new byte[1024];
        try {
            while ((len = inputStream.read(bytes)) != -1) {
                // 将bcache中读取的input数据写入infoStream
                infoStream.write(bytes, 0, len);
            }
        } catch (IOException e1) {
            throw new Exception("输入流读取异常");
        } finally {
            try {
                inputStream.close(); // 输入流关闭
            } catch (IOException e) {
                throw new Exception("输入流关闭异常");
            }
        }
        return infoStream.toString();
    }


}
